package com.chixing.dao.impl;

import com.chixing.dao.DepartmentDao;
import com.chixing.entity.Department;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDaoImpl implements DepartmentDao {

    @Override
    public int insert(Department department) {
//1. 连接数据库
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//加载数据库驱动类
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        String url="jdbc:mysql://localhost:3306/empdb?useUnicode=true&characterEncoding=UTF-8";
        String username="root";
        String password="root";
        Connection connection=null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功!");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
//2. 执行sql语句
        String sql="insert into dept values(?,?,?)";
        PreparedStatement pstmt=null;
        try {
            pstmt = connection.prepareStatement(sql);
            //给占位符赋值
            pstmt.setInt(1,department.getDeptNo());
            pstmt.setString(2,department.getDeptName());
            pstmt.setString(3,department.getDeptLoc());
            //3. 获得结果
            int rows = pstmt.executeUpdate();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            //4. 关闭连接
            if (pstmt!=null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }

    }

    @Override
    public int update(Department department) {
        return 0;
    }

    @Override
    public int delete(int deptNo) {
        return 0;
    }

    @Override
    public Department selectByNo(int deptno) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        String url="jdbc:mysql://localhost:3306/empdb?useUnicode=true&characterEncoding=UTF-8";
        String username="root";
        String password="root";
        Connection connection =null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        String sql="select deprno,dename,loc from dept where deptno=?";
        PreparedStatement preparedStatement =null;
        ResultSet rs =null;
        Department department =null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,deptno);
            rs = preparedStatement.executeQuery();
            while (rs.next()){
                int deptNo = rs.getInt(1);
                String dname = rs.getString(2);
                String loc = rs.getString(3);
                department = new Department(deptNo, dname, loc);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return department;
    }

    @Override
    public List<Department> selectAll() {
        List<Department> departmentList=new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        String url="jdbc:mysql://localhost:3306/empdb?useUnicode=true&characterEncoding=UTF-8";
        String username="root";
        String password="root";
        Connection connection =null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功!");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        String sql="select deptno,dname,loc from dept ";
        PreparedStatement preparedStatement =null;
        ResultSet rs =null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()){
                int deptNo = rs.getInt(1);
                String dname = rs.getString(2);
                String loc = rs.getString(3);
                Department department=new Department(deptNo,dname,loc);
                departmentList.add(department);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (preparedStatement!=null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return departmentList;
    }
}
